{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Wrangling\n",
    "these functions below transform data from different formats into a single common format,  \n",
    "appends the transformed data to either ShellCollection.txt, SQLCollection.txt, XSSCollection.txt or non-maliciousCollection.txt, depending on type.  \n",
    "The last function in the notebook combines the text files into a single .csv file\n",
    "\n",
    "P.S! The source data files aren't included, so no need to run these scripts. You should start testing the project by starting in the Data Cleaning notebook!\n",
    "\n",
    "Source to original data:  \n",
    "https://github.com/foospidy/payloads/blob/master/get.sh  \n",
    "http://www.isi.csic.es/dataset/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step1\n",
    "import dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import csv\n",
    "import re\n",
    "import json\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Step2\n",
    "tranform data from source data set formats into the right format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of SQL injection data points: 286\n",
      "First 5 SQL injection data points:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "432          1;DROP TABLE users\n",
       "433    1'; DROP TABLE users-- 1\n",
       "434               ' OR 1=1 -- 1\n",
       "435                 ' OR '1'='1\n",
       "760                 ’ or ‘1’=’1\n",
       "Name: Payload, dtype: object"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of XSS injection data points: 1115\n",
      "First 5 XSS injection data points:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0                     script>alert(123)</script>\n",
       "1      <script>alert(\"hellox worldss\");</script>\n",
       "2             javascript:alert(\"hellox worldss\")\n",
       "3           <img src=\"javascript:alert('XSS');\">\n",
       "4    <img src=javascript:alert(&quot;XSS&quot;)>\n",
       "Name: Payload, dtype: object"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def from_google_spreadsheet_to_collections(file):\n",
    "    '''Converts web traffic payloads from csv file to right format into collections \n",
    "\n",
    "    the input format of the data points are:\n",
    "    <is malicious>,<Injection type>,<Payload>\n",
    "    '''\n",
    "    \n",
    "    df = pd.read_csv(\"data/{}.csv\".format(file))\n",
    "    \n",
    "    #extract injection data\n",
    "    sql_data  = df['Payload'][df['Injection Type'] == 'SQL']\n",
    "    xss_data  = df['Payload'][df['Injection Type'] == 'XSS']\n",
    "\n",
    "    print('Number of SQL injection data points: ' + str(len(sql_data)))\n",
    "    print('First 5 SQL injection data points:')\n",
    "    display(sql_data[:5])\n",
    "\n",
    "    print('Number of XSS injection data points: ' + str(len(xss_data)))\n",
    "    print('First 5 XSS injection data points:')\n",
    "    display(xss_data[:5])\n",
    "    \n",
    "    with open(\"data/SQLCollection.txt\", \"a\") as myfile:\n",
    "        for sql_row in sql_data:\n",
    "            myfile.write('{}\\n'.format(sql_row.encode(\"utf-8\")))\n",
    "            \n",
    "    with open(\"data/XSSCollection.txt\",\"a\") as myfile:\n",
    "        for xss_row in xss_data:\n",
    "            myfile.write('{}\\n'.format(xss_row.encode(\"utf-8\")))\n",
    "    pass      \n",
    "\n",
    "#IPS_payload_data is our spreadsheet of payloads gathered so far\n",
    "from_google_spreadsheet_to_collections('IPS_payload_data')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "raw data in source file format: Directory Traversal - For Unix##/../../../../file##0\n",
      "\n",
      "modified data in right format: /../../../../file\n",
      " 91\n"
     ]
    }
   ],
   "source": [
    "def from_xsuperbug_to_collections(src_file, dest_file):\n",
    "    '''Converts web traffic payloads from xsuperbug's format to the right format into collections \n",
    "    \n",
    "    the input format of the data points are:\n",
    "    <injections type>##<Payload>##<number>\n",
    "    '''\n",
    "    \n",
    "    lines = open(\"data/{}\".format(src_file),\"r\").readlines()\n",
    "    print('raw data in source file format: ' + lines[0])\n",
    "    lines = [ re.search(r'(.*)##(.*)##[0-9]',line).group(2) for line in lines]\n",
    "    print('modified data in right format: ' + lines[0])\n",
    "    print(' ' + str(len(lines)))\n",
    "    \n",
    "    with open(\"data/{}\".format(dest_file), \"a\") as myfile:\n",
    "        for line in lines:\n",
    "            myfile.write('{}\\n'.format(line.encode(\"utf-8\")))\n",
    "    \n",
    "#from_xsuperbug_to_collections('timetoparseSQL.txt','SQLCollection.txt')\n",
    "#from_xsuperbug_to_collections('timetoparseXSS.txt','XSSCollection.txt')\n",
    "from_xsuperbug_to_collections('timetoparseCMD.txt','ShellCollection.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def from_cnets_to_collection(src_file, dest_file):\n",
    "    '''Converts web traffic payloads from CNetS' web traffic data set format to the right format into collections\n",
    "    \n",
    "    source data set found here: http://cnets.indiana.edu/resources/data-repository/\n",
    "    the input file is in JSON format and the input format of the data points are:\n",
    "    {\"count\": <number>, \"timestamp\": <Date>, \"from\": \"<Website>/<Payload1>\", \"to\": \"<Website>/<Payload2>\"}\n",
    "    '''\n",
    "    raw_data = []\n",
    "    \n",
    "    with open(\"data/{}.json\".format(src_file)) as f:\n",
    "        for line in f.readlines():\n",
    "            raw_data.append(json.loads(line))\n",
    "    \n",
    "    #Extract 'from' and 'to' columns\n",
    "    data = pd.Series([obj['from'] for obj in raw_data] + [obj['to'] for obj in raw_data]) \n",
    "    \n",
    "    #Remove empty elements\n",
    "    data = data[data != '']\n",
    "    \n",
    "    \n",
    "    #Extract data containing payloads, i.e. containing the '=' sign followed by a word\n",
    "    data = data[ [re.match(r'(.*)=(.+)',x) != None for x in data] ]\n",
    "    \n",
    "    payloads = []\n",
    "    \n",
    "    #extract each input from the entire payload string\n",
    "    for payload in data:\n",
    "        temp = payload.split('&')\n",
    "        payloads = payloads + [substring.split('=')[1] for substring in temp if len(substring.split('=')) > 1]\n",
    "    \n",
    "    #write to destination file\n",
    "    with open(\"data/{}\".format(dest_file), \"a\") as myfile:\n",
    "        for payload in payloads:\n",
    "            if payload != '':\n",
    "                myfile.write('{}\\n'.format(payload))\n",
    "\n",
    "#There are 21 files with non-malicious payloads, each with its date as name\n",
    "for i in range(1,22):\n",
    "    date = '0' + str(i) if i < 10  else str(i)\n",
    "    from_cnets_to_collection('2009-11-{}'.format(date),'non-maliciousCollection.txt')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total payloads found: 2929\n",
      "First 20 payloads:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['',\n",
       " '(173-95)',\n",
       " '1331994300888',\n",
       " 'results',\n",
       " '1331736235',\n",
       " '(63245-posstr(chr(97),chr(97))+53475)',\n",
       " 'otcymtc0',\n",
       " 'ping\\\\x0c-w\\\\x0c7000\\\\x0c-n\\\\x0c1\\\\x0c1.2.3.4',\n",
       " '1331910623.41',\n",
       " 'rm\\\\x09q92179245',\n",
       " 'del\\\\x0cq61251932',\n",
       " '1331749591',\n",
       " 'ping,-w,11000,-n,1,4.3.2.1|rem,',\n",
       " 'ota3nzyw@',\n",
       " 'register',\n",
       " 'http://xxxxxxxx/1',\n",
       " 'linpha_order_sql_injection.nasl_1332008614',\n",
       " '\\\\x0ddel q74771226 #',\n",
       " '1331884972314',\n",
       " 'all']"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def from_fsecurify_to_collection(src_file, dest_file):\n",
    "    '''Extracts payload data inputs from address strings\n",
    "    \n",
    "    source data set found here: \n",
    "    https://raw.githubusercontent.com/faizann24/Fwaf-Machine-Learning-driven-Web-Application-Firewall/master/goodqueries.txt\n",
    "    \n",
    "    the format of the data points are:\n",
    "    <Website local path>?<Payload>\n",
    "    example: folder1/folder2?var1=payloadData\n",
    "    '''\n",
    "    payloads = []\n",
    "    \n",
    "    with open(\"data/{}\".format(src_file)) as f:\n",
    "        for line in f.readlines():\n",
    "            splitted_address = line.split('?')\n",
    "            \n",
    "            #if there is payload\n",
    "            if len(splitted_address) > 1:\n",
    "                total_payload = splitted_address[1]\n",
    "                temp = total_payload.split('&')\n",
    "                \n",
    "                #Add all input data from payload \n",
    "                #exclude input that contains http://192.168.202 (these were strange local queries)\n",
    "                #exclude input that contains the word 'select' AND 'union' (these were actually malicious)\n",
    "                payloads = payloads + [substring.split('=')[1].strip('\\n') for substring in temp \n",
    "                                       if len(substring.split('=')) > 1 and\n",
    "                                       'http://192.168.202' not in substring.split('=')[1] and\n",
    "                                       ('select' not in substring.split('=')[1] or 'union' not in substring.split('=')[1])\n",
    "                                      ]\n",
    "    #remove duplicates\n",
    "    payloads = list(set(payloads))\n",
    "                \n",
    "    #write to destination file\n",
    "    with open(\"data/{}\".format(dest_file), \"a\") as myfile:\n",
    "        for payload in payloads:\n",
    "            if payload != '':\n",
    "                myfile.write('{}\\n'.format(payload))\n",
    "        \n",
    "    print('Total payloads found: '+str(len(payloads)))\n",
    "    print('First 20 payloads:')\n",
    "    display(payloads[:20])\n",
    "\n",
    "        \n",
    "from_fsecurify_to_collection('goodqueries.txt','non-maliciousCollection.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of data points gathered: 19344\n",
      "First 20 data points:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['shuster',\n",
       " '3288111380573813',\n",
       " '7315',\n",
       " 'Calle+Montesol+30%2C+',\n",
       " 'woolwine0',\n",
       " '25742430W',\n",
       " 'Ramiro+De+Maeztu%2C+S%2FN+8C',\n",
       " '02054707W',\n",
       " 'violet%40sunmiles.cf',\n",
       " 'oxley%40muevetuweb.lb',\n",
       " 'arena%40productosgarantizados.uy',\n",
       " 'cioffi',\n",
       " '7961872329809538',\n",
       " '8622247853302054',\n",
       " 'roquemore%40veocime.bu',\n",
       " 'oke',\n",
       " 'oblong0',\n",
       " 'Del+Barrio',\n",
       " 'cEgaJOSo',\n",
       " 'shashi']"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of data points gathered: 20035\n",
      "First 20 data points:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['Sigrid',\n",
       " 'Calle+Osa+Mayor+S%2FN%2C+4-G',\n",
       " '09348',\n",
       " 'Ataquines',\n",
       " '7739977532136253',\n",
       " '7315',\n",
       " '2340',\n",
       " 'Presentaci%F3n',\n",
       " 'Mallabia',\n",
       " '7539210731606782',\n",
       " 'kirkland',\n",
       " 'Kimberley',\n",
       " '54375556Z',\n",
       " '51928951B',\n",
       " 'darfeuil%40naturalchild.lc',\n",
       " 'hsaio%40suecas.com.ec',\n",
       " '4587377607226524',\n",
       " 'medeiros',\n",
       " 'toshi',\n",
       " 'Edesio']"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def from_CSIC2010_to_collection(src_file, dest_file):\n",
    "    '''Extracts payload data inputs from CSIC2010 HTTP packet dataset\n",
    "    \n",
    "    source dataset found here: http://www.isi.csic.es/dataset/\n",
    "    input format from source is a complete HTTP packet\n",
    "    '''\n",
    "    \n",
    "    payloads = []\n",
    "    payload_next_line = False\n",
    "    \n",
    "    with open(\"data/{}\".format(src_file)) as f:\n",
    "        for line in f.readlines():\n",
    "            \n",
    "            #Extract inputs from payload if first row in a GET packet\n",
    "            if line.startswith('GET') and len(line.split('?')) > 1:\n",
    "                \n",
    "                #extract total payload string\n",
    "                total_payload = (line.split('?')[1]).split(' ')[0]\n",
    "                \n",
    "                #add each input value separately to payloads\n",
    "                inputs = total_payload.split('&')\n",
    "                payloads = payloads + [input.split('=')[1] for input in inputs if len(input.split('=')) > 1]\n",
    "                \n",
    "            if line.startswith('Content-Length'):\n",
    "                #notify that this is a HTTP POST packet and the next line will contain the payload\n",
    "                payload_next_line = True\n",
    "                \n",
    "            elif payload_next_line and len(line) > 2:\n",
    "                #Current line is a payload of a HTTP POST packet\n",
    "                \n",
    "                #add each input value separately to payloads\n",
    "                inputs = line.split('&')\n",
    "                payloads = payloads + [input.split('=')[1].strip('\\n') for input in inputs if len(input.split('=')) > 1]\n",
    "                \n",
    "                payload_next_line = False\n",
    "       \n",
    "    payloads = list(set(payloads))\n",
    "\n",
    "    #write to destination file\n",
    "    with open(\"data/{}\".format(dest_file), \"a\") as myfile:\n",
    "        for payload in payloads:\n",
    "            if payload != '':\n",
    "                myfile.write('{}\\n'.format(payload))\n",
    "\n",
    "    print('Total number of data points gathered: ' + str(len(payloads)))\n",
    "    print('First 20 data points:')\n",
    "    display(payloads[:20])\n",
    "    \n",
    "from_CSIC2010_to_collection('normalTrafficTraining.txt','non-maliciousCollection.txt')\n",
    "from_CSIC2010_to_collection('normalTrafficTest.txt','non-maliciousCollection.txt')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
